{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:35.479475Z",
     "start_time": "2020-03-21T07:58:34.886355Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "import os\n",
    "from tqdm import tqdm\n",
    "from sklearn import preprocessing, metrics\n",
    "import lightgbm as lgb\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from joblib import Parallel, delayed\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:38.217763Z",
     "start_time": "2020-03-21T07:58:35.481049Z"
    }
   },
   "outputs": [],
   "source": [
    "df_history_action = pd.read_pickle('./temp/action_history.plk')\n",
    "df_feature = pd.read_pickle('./temp/base_feature.plk')\n",
    "df_courier = pd.read_pickle('./temp/courier.plk')\n",
    "df_order = pd.read_pickle('./temp/order.plk')\n",
    "df_distance = pd.read_pickle('./temp/distance.plk')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:38.221070Z",
     "start_time": "2020-03-21T07:58:38.219253Z"
    }
   },
   "outputs": [],
   "source": [
    "seed = 2020"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 历史订单信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:38.237211Z",
     "start_time": "2020-03-21T07:58:38.222071Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>courier_id</th>\n",
       "      <th>wave_index</th>\n",
       "      <th>tracking_id</th>\n",
       "      <th>courier_wave_start_lng</th>\n",
       "      <th>courier_wave_start_lat</th>\n",
       "      <th>action_type</th>\n",
       "      <th>expect_time</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074548854622111</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580527779</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>20200201100078710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074548854622111</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580528077</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>20200201100078710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>20200201100078710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074553896437081</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580530391</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>20200201100078711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074553896437081</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580531150</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>20200201100078711</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     courier_id  wave_index          tracking_id  courier_wave_start_lng  \\\n",
       "120    10007871           0  2100074548854622111              121.630997   \n",
       "121    10007871           0  2100074548854622111              121.630997   \n",
       "122    10007871           0  2100074550065333539              121.630997   \n",
       "126    10007871           1  2100074553896437081              121.631208   \n",
       "127    10007871           1  2100074553896437081              121.631208   \n",
       "\n",
       "     courier_wave_start_lat action_type  expect_time      date   type  \\\n",
       "120               39.142343      PICKUP   1580527779  20200201  train   \n",
       "121               39.142343    DELIVERY   1580528077  20200201  train   \n",
       "122               39.142343      PICKUP   1580528622  20200201  train   \n",
       "126               39.142519      PICKUP   1580530391  20200201  train   \n",
       "127               39.142519    DELIVERY   1580531150  20200201  train   \n",
       "\n",
       "                 group  \n",
       "120  20200201100078710  \n",
       "121  20200201100078710  \n",
       "122  20200201100078710  \n",
       "126  20200201100078711  \n",
       "127  20200201100078711  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_history_action.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:49.477669Z",
     "start_time": "2020-03-21T07:58:38.238445Z"
    }
   },
   "outputs": [],
   "source": [
    "# 获取 wave 最后一次 step 信息\n",
    "df_temp = df_history_action.groupby(['group'])['expect_time'].apply(\n",
    "    lambda x: x.values.tolist()[-1]).reset_index()\n",
    "df_temp.columns = ['group', 'current_time']\n",
    "df_feature = df_feature.merge(df_temp, how='left')\n",
    "\n",
    "df_temp = df_history_action.groupby(['group'])['tracking_id'].apply(\n",
    "    lambda x: x.values.tolist()[-1]).reset_index()\n",
    "df_temp.columns = ['group', 'last_tracking_id']\n",
    "df_feature = df_feature.merge(df_temp, how='left')\n",
    "\n",
    "df_temp = df_history_action.groupby(['group'])['action_type'].apply(\n",
    "    lambda x: x.values.tolist()[-1]).reset_index()\n",
    "df_temp.columns = ['group', 'last_action_type']\n",
    "df_feature = df_feature.merge(df_temp, how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# distance 表相关特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:49.490819Z",
     "start_time": "2020-03-21T07:58:49.479120Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>courier_id</th>\n",
       "      <th>wave_index</th>\n",
       "      <th>tracking_id</th>\n",
       "      <th>source_type</th>\n",
       "      <th>source_lng</th>\n",
       "      <th>source_lat</th>\n",
       "      <th>target_tracking_id</th>\n",
       "      <th>target_type</th>\n",
       "      <th>target_lng</th>\n",
       "      <th>target_lat</th>\n",
       "      <th>grid_distance</th>\n",
       "      <th>date</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074683194934900</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.62614</td>\n",
       "      <td>39.134413</td>\n",
       "      <td>2100074669847643396</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.623880</td>\n",
       "      <td>39.133604</td>\n",
       "      <td>211.0</td>\n",
       "      <td>20200204</td>\n",
       "      <td>202002041000025430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074683194934900</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.62614</td>\n",
       "      <td>39.134413</td>\n",
       "      <td>2100074669847643396</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.627230</td>\n",
       "      <td>39.134409</td>\n",
       "      <td>118.0</td>\n",
       "      <td>20200204</td>\n",
       "      <td>202002041000025430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074683194934900</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.62614</td>\n",
       "      <td>39.134413</td>\n",
       "      <td>2100074683510555680</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.627417</td>\n",
       "      <td>39.133745</td>\n",
       "      <td>421.0</td>\n",
       "      <td>20200204</td>\n",
       "      <td>202002041000025430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074683194934900</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.62614</td>\n",
       "      <td>39.134413</td>\n",
       "      <td>2100074669847643396</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>121.648664</td>\n",
       "      <td>39.138861</td>\n",
       "      <td>3015.0</td>\n",
       "      <td>20200204</td>\n",
       "      <td>202002041000025430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074683194934900</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.62614</td>\n",
       "      <td>39.134413</td>\n",
       "      <td>2100074683510555680</td>\n",
       "      <td>ASSIGN</td>\n",
       "      <td>121.625230</td>\n",
       "      <td>39.133738</td>\n",
       "      <td>205.0</td>\n",
       "      <td>20200204</td>\n",
       "      <td>202002041000025430</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   courier_id  wave_index          tracking_id source_type  source_lng  \\\n",
       "0   100002543           0  2100074683194934900      ASSIGN   121.62614   \n",
       "1   100002543           0  2100074683194934900      ASSIGN   121.62614   \n",
       "2   100002543           0  2100074683194934900      ASSIGN   121.62614   \n",
       "3   100002543           0  2100074683194934900      ASSIGN   121.62614   \n",
       "4   100002543           0  2100074683194934900      ASSIGN   121.62614   \n",
       "\n",
       "   source_lat   target_tracking_id target_type  target_lng  target_lat  \\\n",
       "0   39.134413  2100074669847643396      ASSIGN  121.623880   39.133604   \n",
       "1   39.134413  2100074669847643396      PICKUP  121.627230   39.134409   \n",
       "2   39.134413  2100074683510555680      PICKUP  121.627417   39.133745   \n",
       "3   39.134413  2100074669847643396    DELIVERY  121.648664   39.138861   \n",
       "4   39.134413  2100074683510555680      ASSIGN  121.625230   39.133738   \n",
       "\n",
       "   grid_distance      date               group  \n",
       "0          211.0  20200204  202002041000025430  \n",
       "1          118.0  20200204  202002041000025430  \n",
       "2          421.0  20200204  202002041000025430  \n",
       "3         3015.0  20200204  202002041000025430  \n",
       "4          205.0  20200204  202002041000025430  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_distance.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.076776Z",
     "start_time": "2020-03-21T07:58:49.492449Z"
    }
   },
   "outputs": [],
   "source": [
    "df_distance = df_distance.rename(columns={'tracking_id': 'last_tracking_id',\n",
    "                                          'source_type': 'last_action_type', 'target_tracking_id': 'tracking_id', 'target_type': 'action_type'})\n",
    "df_feature = df_feature.merge(df_distance.drop(\n",
    "    ['courier_id', 'wave_index', 'date'], axis=1), how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.093334Z",
     "start_time": "2020-03-21T07:58:58.078270Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>courier_id</th>\n",
       "      <th>wave_index</th>\n",
       "      <th>tracking_id</th>\n",
       "      <th>courier_wave_start_lng</th>\n",
       "      <th>courier_wave_start_lat</th>\n",
       "      <th>action_type</th>\n",
       "      <th>expect_time</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>target</th>\n",
       "      <th>group</th>\n",
       "      <th>id</th>\n",
       "      <th>current_time</th>\n",
       "      <th>last_tracking_id</th>\n",
       "      <th>last_action_type</th>\n",
       "      <th>source_lng</th>\n",
       "      <th>source_lat</th>\n",
       "      <th>target_lng</th>\n",
       "      <th>target_lat</th>\n",
       "      <th>grid_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580528963</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>0</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.632084</td>\n",
       "      <td>39.146201</td>\n",
       "      <td>707.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550779577850</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580529129</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>1</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.631574</td>\n",
       "      <td>39.142231</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550779577850</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580529444</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>2</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.635154</td>\n",
       "      <td>39.143561</td>\n",
       "      <td>671.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074555638285402</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580532225</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200201100078711</td>\n",
       "      <td>3</td>\n",
       "      <td>1580532113</td>\n",
       "      <td>2100074554932692192</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>121.636904</td>\n",
       "      <td>39.142721</td>\n",
       "      <td>121.636701</td>\n",
       "      <td>39.141801</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074554118800474</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580532227</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078711</td>\n",
       "      <td>4</td>\n",
       "      <td>1580532113</td>\n",
       "      <td>2100074554932692192</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>121.636904</td>\n",
       "      <td>39.142721</td>\n",
       "      <td>121.636701</td>\n",
       "      <td>39.141801</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   courier_id  wave_index          tracking_id  courier_wave_start_lng  \\\n",
       "0    10007871           0  2100074550065333539              121.630997   \n",
       "1    10007871           0  2100074550779577850              121.630997   \n",
       "2    10007871           0  2100074550779577850              121.630997   \n",
       "3    10007871           1  2100074555638285402              121.631208   \n",
       "4    10007871           1  2100074554118800474              121.631208   \n",
       "\n",
       "   courier_wave_start_lat action_type  expect_time      date   type  target  \\\n",
       "0               39.142343    DELIVERY   1580528963  20200201  train     1.0   \n",
       "1               39.142343      PICKUP   1580529129  20200201  train     0.0   \n",
       "2               39.142343    DELIVERY   1580529444  20200201  train     0.0   \n",
       "3               39.142519      PICKUP   1580532225  20200201  train     1.0   \n",
       "4               39.142519      PICKUP   1580532227  20200201  train     0.0   \n",
       "\n",
       "               group  id  current_time     last_tracking_id last_action_type  \\\n",
       "0  20200201100078710   0    1580528622  2100074550065333539           PICKUP   \n",
       "1  20200201100078710   1    1580528622  2100074550065333539           PICKUP   \n",
       "2  20200201100078710   2    1580528622  2100074550065333539           PICKUP   \n",
       "3  20200201100078711   3    1580532113  2100074554932692192         DELIVERY   \n",
       "4  20200201100078711   4    1580532113  2100074554932692192         DELIVERY   \n",
       "\n",
       "   source_lng  source_lat  target_lng  target_lat  grid_distance  \n",
       "0  121.631219   39.141811  121.632084   39.146201          707.0  \n",
       "1  121.631219   39.141811  121.631574   39.142231          152.0  \n",
       "2  121.631219   39.141811  121.635154   39.143561          671.0  \n",
       "3  121.636904   39.142721  121.636701   39.141801          160.0  \n",
       "4  121.636904   39.142721  121.636701   39.141801          160.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# order 表相关特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.105914Z",
     "start_time": "2020-03-21T07:58:58.094423Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>courier_id</th>\n",
       "      <th>wave_index</th>\n",
       "      <th>tracking_id</th>\n",
       "      <th>weather_grade</th>\n",
       "      <th>pick_lng</th>\n",
       "      <th>pick_lat</th>\n",
       "      <th>deliver_lng</th>\n",
       "      <th>deliver_lat</th>\n",
       "      <th>create_time</th>\n",
       "      <th>confirm_time</th>\n",
       "      <th>assigned_time</th>\n",
       "      <th>promise_deliver_time</th>\n",
       "      <th>estimate_pick_time</th>\n",
       "      <th>aoi_id</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100075423059314102</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>121.631386</td>\n",
       "      <td>39.134184</td>\n",
       "      <td>121.621114</td>\n",
       "      <td>39.133431</td>\n",
       "      <td>1582256837</td>\n",
       "      <td>1582257379</td>\n",
       "      <td>1582257409</td>\n",
       "      <td>1582259597</td>\n",
       "      <td>1582258457</td>\n",
       "      <td>3f3df1b8862af65746bb49609eeb57c7</td>\n",
       "      <td>5c9c9ca9271ff717255d2068c1bb79ed</td>\n",
       "      <td>20200221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002543</td>\n",
       "      <td>0</td>\n",
       "      <td>2100075422789371468</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>121.629854</td>\n",
       "      <td>39.134711</td>\n",
       "      <td>121.607654</td>\n",
       "      <td>39.128001</td>\n",
       "      <td>1582257790</td>\n",
       "      <td>1582257791</td>\n",
       "      <td>1582257830</td>\n",
       "      <td>1582260310</td>\n",
       "      <td>1582258691</td>\n",
       "      <td>427956eae5d8bf0cda42593f3ac4d8fc</td>\n",
       "      <td>18e2305e99dbc89aaec8dac3d89c5a85</td>\n",
       "      <td>20200221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100002652</td>\n",
       "      <td>0</td>\n",
       "      <td>2100075415175234624</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>121.566356</td>\n",
       "      <td>39.149935</td>\n",
       "      <td>121.548444</td>\n",
       "      <td>39.145661</td>\n",
       "      <td>1582252549</td>\n",
       "      <td>1582252550</td>\n",
       "      <td>1582252569</td>\n",
       "      <td>1582255249</td>\n",
       "      <td>1582253990</td>\n",
       "      <td>4670fa58c5d3942faf437a008b4cc934</td>\n",
       "      <td>adfe4aafa9da1cc27b7762cc082be000</td>\n",
       "      <td>20200221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100002652</td>\n",
       "      <td>0</td>\n",
       "      <td>2100075416130749977</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>121.549950</td>\n",
       "      <td>39.150131</td>\n",
       "      <td>121.545994</td>\n",
       "      <td>39.146361</td>\n",
       "      <td>1582254125</td>\n",
       "      <td>1582254126</td>\n",
       "      <td>1582254190</td>\n",
       "      <td>1582257365</td>\n",
       "      <td>1582255206</td>\n",
       "      <td>ad8222e53243dd2a7f9440278ca6a5f9</td>\n",
       "      <td>efe575b81c922d8cc0e1da84d1639ccc</td>\n",
       "      <td>20200221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100002652</td>\n",
       "      <td>0</td>\n",
       "      <td>2100075418091685090</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>121.549914</td>\n",
       "      <td>39.150209</td>\n",
       "      <td>121.545834</td>\n",
       "      <td>39.150411</td>\n",
       "      <td>1582254391</td>\n",
       "      <td>1582254402</td>\n",
       "      <td>1582254430</td>\n",
       "      <td>1582256191</td>\n",
       "      <td>1582255122</td>\n",
       "      <td>f56e126110ecda18f474a71a2b2a8604</td>\n",
       "      <td>c1602f198a89acf920c147f98b17bad9</td>\n",
       "      <td>20200221</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   courier_id  wave_index          tracking_id weather_grade    pick_lng  \\\n",
       "0   100002543           0  2100075423059314102          正常天气  121.631386   \n",
       "1   100002543           0  2100075422789371468          正常天气  121.629854   \n",
       "2   100002652           0  2100075415175234624          正常天气  121.566356   \n",
       "3   100002652           0  2100075416130749977          正常天气  121.549950   \n",
       "4   100002652           0  2100075418091685090          正常天气  121.549914   \n",
       "\n",
       "    pick_lat  deliver_lng  deliver_lat  create_time  confirm_time  \\\n",
       "0  39.134184   121.621114    39.133431   1582256837    1582257379   \n",
       "1  39.134711   121.607654    39.128001   1582257790    1582257791   \n",
       "2  39.149935   121.548444    39.145661   1582252549    1582252550   \n",
       "3  39.150131   121.545994    39.146361   1582254125    1582254126   \n",
       "4  39.150209   121.545834    39.150411   1582254391    1582254402   \n",
       "\n",
       "   assigned_time  promise_deliver_time  estimate_pick_time  \\\n",
       "0     1582257409            1582259597          1582258457   \n",
       "1     1582257830            1582260310          1582258691   \n",
       "2     1582252569            1582255249          1582253990   \n",
       "3     1582254190            1582257365          1582255206   \n",
       "4     1582254430            1582256191          1582255122   \n",
       "\n",
       "                             aoi_id                           shop_id  \\\n",
       "0  3f3df1b8862af65746bb49609eeb57c7  5c9c9ca9271ff717255d2068c1bb79ed   \n",
       "1  427956eae5d8bf0cda42593f3ac4d8fc  18e2305e99dbc89aaec8dac3d89c5a85   \n",
       "2  4670fa58c5d3942faf437a008b4cc934  adfe4aafa9da1cc27b7762cc082be000   \n",
       "3  ad8222e53243dd2a7f9440278ca6a5f9  efe575b81c922d8cc0e1da84d1639ccc   \n",
       "4  f56e126110ecda18f474a71a2b2a8604  c1602f198a89acf920c147f98b17bad9   \n",
       "\n",
       "       date  \n",
       "0  20200221  \n",
       "1  20200221  \n",
       "2  20200221  \n",
       "3  20200221  \n",
       "4  20200221  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.308880Z",
     "start_time": "2020-03-21T07:58:58.106891Z"
    }
   },
   "outputs": [],
   "source": [
    "df_feature = df_feature.merge(\n",
    "    df_order[['tracking_id', 'weather_grade', 'aoi_id', 'shop_id', 'promise_deliver_time',\n",
    "              'estimate_pick_time']], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.326768Z",
     "start_time": "2020-03-21T07:58:58.310017Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>courier_id</th>\n",
       "      <th>wave_index</th>\n",
       "      <th>tracking_id</th>\n",
       "      <th>courier_wave_start_lng</th>\n",
       "      <th>courier_wave_start_lat</th>\n",
       "      <th>action_type</th>\n",
       "      <th>expect_time</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>target</th>\n",
       "      <th>group</th>\n",
       "      <th>id</th>\n",
       "      <th>current_time</th>\n",
       "      <th>last_tracking_id</th>\n",
       "      <th>last_action_type</th>\n",
       "      <th>source_lng</th>\n",
       "      <th>source_lat</th>\n",
       "      <th>target_lng</th>\n",
       "      <th>target_lat</th>\n",
       "      <th>grid_distance</th>\n",
       "      <th>weather_grade</th>\n",
       "      <th>aoi_id</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>promise_deliver_time</th>\n",
       "      <th>estimate_pick_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580528963</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>0</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.632084</td>\n",
       "      <td>39.146201</td>\n",
       "      <td>707.0</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>b71df7214347524a0f5f0c79dfdf2f4e</td>\n",
       "      <td>88ac051764fe348382e6529948de8015</td>\n",
       "      <td>1580530276</td>\n",
       "      <td>1580529019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550779577850</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580529129</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>1</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.631574</td>\n",
       "      <td>39.142231</td>\n",
       "      <td>152.0</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>753c1911e8a294c5db901f8555faff0c</td>\n",
       "      <td>92ec52685bd511da262ee6f7a0adaa87</td>\n",
       "      <td>1580530236</td>\n",
       "      <td>1580529399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10007871</td>\n",
       "      <td>0</td>\n",
       "      <td>2100074550779577850</td>\n",
       "      <td>121.630997</td>\n",
       "      <td>39.142343</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>1580529444</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078710</td>\n",
       "      <td>2</td>\n",
       "      <td>1580528622</td>\n",
       "      <td>2100074550065333539</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>121.631219</td>\n",
       "      <td>39.141811</td>\n",
       "      <td>121.635154</td>\n",
       "      <td>39.143561</td>\n",
       "      <td>671.0</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>753c1911e8a294c5db901f8555faff0c</td>\n",
       "      <td>92ec52685bd511da262ee6f7a0adaa87</td>\n",
       "      <td>1580530236</td>\n",
       "      <td>1580529399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074555638285402</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580532225</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200201100078711</td>\n",
       "      <td>3</td>\n",
       "      <td>1580532113</td>\n",
       "      <td>2100074554932692192</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>121.636904</td>\n",
       "      <td>39.142721</td>\n",
       "      <td>121.636701</td>\n",
       "      <td>39.141801</td>\n",
       "      <td>160.0</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>898aef0932f6aaecda27aba8e9903991</td>\n",
       "      <td>1af41a72adbdb2abd7a2dab03e357bcf</td>\n",
       "      <td>1580533463</td>\n",
       "      <td>1580532384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10007871</td>\n",
       "      <td>1</td>\n",
       "      <td>2100074554118800474</td>\n",
       "      <td>121.631208</td>\n",
       "      <td>39.142519</td>\n",
       "      <td>PICKUP</td>\n",
       "      <td>1580532227</td>\n",
       "      <td>20200201</td>\n",
       "      <td>train</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20200201100078711</td>\n",
       "      <td>4</td>\n",
       "      <td>1580532113</td>\n",
       "      <td>2100074554932692192</td>\n",
       "      <td>DELIVERY</td>\n",
       "      <td>121.636904</td>\n",
       "      <td>39.142721</td>\n",
       "      <td>121.636701</td>\n",
       "      <td>39.141801</td>\n",
       "      <td>160.0</td>\n",
       "      <td>正常天气</td>\n",
       "      <td>0d109cc0999fa3a108c67cb748b1931f</td>\n",
       "      <td>1af41a72adbdb2abd7a2dab03e357bcf</td>\n",
       "      <td>1580533598</td>\n",
       "      <td>1580532339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   courier_id  wave_index          tracking_id  courier_wave_start_lng  \\\n",
       "0    10007871           0  2100074550065333539              121.630997   \n",
       "1    10007871           0  2100074550779577850              121.630997   \n",
       "2    10007871           0  2100074550779577850              121.630997   \n",
       "3    10007871           1  2100074555638285402              121.631208   \n",
       "4    10007871           1  2100074554118800474              121.631208   \n",
       "\n",
       "   courier_wave_start_lat action_type  expect_time      date   type  target  \\\n",
       "0               39.142343    DELIVERY   1580528963  20200201  train     1.0   \n",
       "1               39.142343      PICKUP   1580529129  20200201  train     0.0   \n",
       "2               39.142343    DELIVERY   1580529444  20200201  train     0.0   \n",
       "3               39.142519      PICKUP   1580532225  20200201  train     1.0   \n",
       "4               39.142519      PICKUP   1580532227  20200201  train     0.0   \n",
       "\n",
       "               group  id  current_time     last_tracking_id last_action_type  \\\n",
       "0  20200201100078710   0    1580528622  2100074550065333539           PICKUP   \n",
       "1  20200201100078710   1    1580528622  2100074550065333539           PICKUP   \n",
       "2  20200201100078710   2    1580528622  2100074550065333539           PICKUP   \n",
       "3  20200201100078711   3    1580532113  2100074554932692192         DELIVERY   \n",
       "4  20200201100078711   4    1580532113  2100074554932692192         DELIVERY   \n",
       "\n",
       "   source_lng  source_lat  target_lng  target_lat  grid_distance  \\\n",
       "0  121.631219   39.141811  121.632084   39.146201          707.0   \n",
       "1  121.631219   39.141811  121.631574   39.142231          152.0   \n",
       "2  121.631219   39.141811  121.635154   39.143561          671.0   \n",
       "3  121.636904   39.142721  121.636701   39.141801          160.0   \n",
       "4  121.636904   39.142721  121.636701   39.141801          160.0   \n",
       "\n",
       "  weather_grade                            aoi_id  \\\n",
       "0          正常天气  b71df7214347524a0f5f0c79dfdf2f4e   \n",
       "1          正常天气  753c1911e8a294c5db901f8555faff0c   \n",
       "2          正常天气  753c1911e8a294c5db901f8555faff0c   \n",
       "3          正常天气  898aef0932f6aaecda27aba8e9903991   \n",
       "4          正常天气  0d109cc0999fa3a108c67cb748b1931f   \n",
       "\n",
       "                            shop_id  promise_deliver_time  estimate_pick_time  \n",
       "0  88ac051764fe348382e6529948de8015            1580530276          1580529019  \n",
       "1  92ec52685bd511da262ee6f7a0adaa87            1580530236          1580529399  \n",
       "2  92ec52685bd511da262ee6f7a0adaa87            1580530236          1580529399  \n",
       "3  1af41a72adbdb2abd7a2dab03e357bcf            1580533463          1580532384  \n",
       "4  1af41a72adbdb2abd7a2dab03e357bcf            1580533598          1580532339  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# courier 表相关特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.391526Z",
     "start_time": "2020-03-21T07:58:58.327703Z"
    }
   },
   "outputs": [],
   "source": [
    "df_feature = df_feature.merge(df_courier, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.560055Z",
     "start_time": "2020-03-21T07:58:58.392590Z"
    }
   },
   "outputs": [],
   "source": [
    "df_feature.to_pickle('./temp/part1_feature.plk')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-21T07:58:58.563916Z",
     "start_time": "2020-03-21T07:58:58.561125Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(223104, 28)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:dm] *",
   "language": "python",
   "name": "conda-env-dm-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
